﻿<%@ WebHandler Language="C#" Class="WriteData" %>

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.IO;
using System.Text;

public class WriteData : IHttpHandler {

    public void ProcessRequest (HttpContext context) {
        var rs = context.Response;
        var rq = context.Request;
        rs.ContentType = "text/plain";

        string ip = "127.0.0.1";
        string usName = "sa";
        string dbpwd = "1105";
        DataTable dta = dat();
        if (dta.Rows.Count > 0)
        {
            ip = dta.Rows[0]["hostIp"].ToString();
            usName = dta.Rows[0]["DbUser"].ToString();
            dbpwd = dta.Rows[0]["DbPwd"].ToString();
        }
        string dbStr = "Data Source=" + ip + ";DataBase=";
        //保存数据库表字段更改
        if (rq["rowDt"] != null && rq["DbName"] != null && rq["TbName"] != null)
        {
            string rowJsn = rq["rowDt"].Trim();
            string dbNm = rq["DbName"].Trim();
            string tbn = rq["TbName"].Trim();
            string clmNm = rq["clumName"].Trim();
            SqlParameter[] sp = { new SqlParameter("@clmNm",clmNm) };
            dbStr += dbNm+";uid=" + usName + ";pwd=" + dbpwd + "";
            int rslt = 0;
            StringBuilder updateSql = new StringBuilder();
            updateSql.Append("use "+dbNm+" if exists(select count(1) from syscolumns where name='" + clmNm + "' and id=object_id('" + tbn + "')) begin ");

            DataTable dt = MethodCls.JsonToDataTable(rowJsn);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if(dt.Rows[i]["primry"].ToString() != "")
                    {
                        updateSql.Append("alter table "+tbn+" drop CONSTRAINT PK_"+tbn+";alter table "+tbn+" drop column " + clmNm + ";");
                    }
                    if (dt.Rows[i]["bs"].ToString() !="")
                    {
                        updateSql.Append("alter table "+tbn+" add " + clmNm + " " +dt.Rows[i]["ctype"]+" identity(1,1);");
                    }
                    else
                    {
                        updateSql.Append(" alter table "+tbn+" ALTER COLUMN "+clmNm+" " +dt.Rows[i]["ctype"]+"");
                    }
                    if (dt.Rows[i]["cLength"]!=null && dt.Rows[i]["ctype"].ToString() !="tinyint" && dt.Rows[i]["ctype"].ToString() !="int" && dt.Rows[i]["ctype"].ToString() !="datetime")
                    {
                        int lenth = int.Parse(dt.Rows[i]["cLength"].ToString());
                        updateSql.Append("(" + lenth + ")");
                    }
                    if(dt.Rows[i]["iNull"].ToString()=="" && dt.Rows[i]["primry"].ToString() == "")
                    {
                        updateSql.Append(" not null");
                    }
                    updateSql.Append(";");
                    if (dt.Rows[i]["mark"] != null && dt.Rows[i]["primry"].ToString() == "")
                    {
                        updateSql.Append("EXEC sp_updateextendedproperty 'MS_Description','"+dt.Rows[i]["mark"]+"','user',dbo,'table',"+tbn+",'Column',"+clmNm+";");
                    }
                    if(dt.Rows[i]["primry"].ToString() != "")
                    {
                        updateSql.Append(" alter table "+tbn+" add CONSTRAINT PK_"+tbn+" primary key ("+clmNm+") ");
                    }
                    updateSql.Append(" end else alter table "+tbn+" Add "+clmNm+" " +dt.Rows[i]["ctype"]+";");

                }
                string sql = updateSql.ToString();
                SqlConnection con = new SqlConnection(dbStr);
                con.Open();
                SqlCommand cmd = new SqlCommand(sql,con);
                rslt = cmd.ExecuteNonQuery();
                con.Close();
                //rslt = SqlHelper.ExecuteNonQuery(dbStr,CommandType.Text,sql,sp);
            }
            rs.Write(rslt);
            rs.End();
        }
        //删除字段
        if (rq["deletClum"] == "yes" && rq["ClumName"] != null && rq["DbName"] != null && rq["TbName"] != null && rq["primry"] != null)
        {
            string clm = rq["ClumName"].Trim();
            string dbNm = rq["DbName"].Trim();
            string tbn = rq["TbName"].Trim();
            int rslt = 0;
            SqlParameter[] sp = { new SqlParameter("@clmNm",clm) };
            dbStr += dbNm+";uid=" + usName + ";pwd=" + dbpwd + "";
            string sql = @"if exists(select count(1) from syscolumns where name='RmTypeId' and id=object_id('tb_RoomType')) begin ";
            if (rq["primry"] !="")
            {
                rs.Write(rslt);
                rs.End();
            }else
            {
                sql += "alter table " + tbn + " drop column " + clm + "; end";
            }
            SqlConnection con = new SqlConnection(dbStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(sql,con);
            rslt = cmd.ExecuteNonQuery();
            con.Close();
            rs.Write(rslt);
            rs.End();
        }
    }

    //获取数据库服务器配置参数
    protected DataTable dat()
    {
        DataSet ds = XMLHelper.rds("App_Data/SystemSetting.xml");
        DataTable dtt = ds.Tables[0];
        return dtt;
    }
    public bool IsReusable {
        get {
            return false;
        }
    }

}